<?php
/**
 * Application level Controller
 *
 * This file is application-wide controller file. You can put all
 * application-wide controller-related methods here.
 *
 * PHP 5
 *
 * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
 * Copyright 2005-2012, Cake Software Foundation, Inc. (http://cakefoundation.org)
 *
 * Licensed under The MIT License
 * Redistributions of files must retain the above copyright notice.
 *
 * @copyright     Copyright 2005-2012, Cake Software Foundation, Inc. (http://cakefoundation.org)
 * @link          http://cakephp.org CakePHP(tm) Project
 * @package       app.Controller
 * @since         CakePHP(tm) v 0.2.9
 * @license       MIT License (http://www.opensource.org/licenses/mit-license.php)
 */

App::uses('Controller', 'Controller');

/**
 * Application Controller
 *
 * Add your application-wide methods in the class below, your controllers
 * will inherit them.
 *
 * @package       app.Controller
 * @link http://book.cakephp.org/2.0/en/controllers.html#the-app-controller
 */
class AppController extends Controller {
 # Set the various components and helpers to use.
    public $helpers = array(
        'Js', 'Html', 'Form', 'Session','Select','Message'
    );

    var $authUser  = null;

    public $components = array(
        'RequestHandler', 'Session',
        'Auth' => array(
            'loginAction' => array('controller' => 'Users', 'action' => 'login'),
            'loginRedirect' => array('controller' => 'Dashboard', 'action' => 'index'),
            'logoutRedirect' => array('controller' => 'Users', 'action' => 'login')
        ),
        'ExcelPHP'
    );

    function beforeFilter($moduleName = null){
		/*if(isset($_POST)){
            $_POST = $this->sanitize($_POST);
        }
        if(isset($_GET)){
            $_GET  = $this->sanitize($_GET);
        }*/
	
        $authUser = $this->authUser = $this->Auth->user();
        $group_modules = $this->getGroupModules();
        $result = $this->validateModules($group_modules);
        if(!$result){//Your group is not permitted to access this module
            $this->redirect(array('controller' => 'Dashboard', 'action' => 'index'));
        }
        $this->set(compact('authUser','group_modules'));
    }
	
	 function cleanInput($input) {
        $search = array(
            '@<script[^>]*?>.*?</script>@si',   // Strip out javascript
            '@<[\/\!]*?[^<>]*?>@si',            // Strip out HTML tags
            '@<style[^>]*?>.*?</style>@siU',    // Strip style tags properly
            '@<![\s\S]*?--[ \t\n\r]*>@'         // Strip multi-line comments
        );
        $output = preg_replace($search, '', $input);
        return $output;
    }

    function sanitize($input) {
        if (is_array($input)) {
            $output = array();
            foreach($input as $var=>$val) {
                $output[$var] = $this->sanitize($val);
            }
        }
        else {
            if (get_magic_quotes_gpc()) {
                $input = stripslashes($input);
            }
            $input  = $this->cleanInput($input);
            $output = mysql_real_escape_string($input);
        }
        return $output;
    }


    protected  function convertToExcel($list_headers,$param ,$filename)
    {
        $objPHPExcel = new PHPExcel();

        // Set document properties
        $objPHPExcel->getProperties()->setCreator("helpdesk")
            ->setLastModifiedBy("helpdesk")
            ->setTitle("Office 2007 XLSX WARP-D helpdesk Document")
            ->setSubject("Office 2007 XLSX helpdesk  Document")
            ->setDescription("helpdesk document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("helpdesk result file");

        // Set default font
        $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(10);

        $count_h = 1;
        foreach($list_headers as $header){
            $letter_h = $this->getAlphabetCharacter($count_h);
            $objPHPExcel->getActiveSheet()->setCellValue($letter_h.'1', $header);
            $count_h++;
        }
        //Bold the headers
        $letter_h = $this->getAlphabetCharacter($count_h -1);
        $objPHPExcel->getActiveSheet()->getStyle('A1:'.$letter_h.'1')->getFont()->setBold(true);

        $it = 2;
        foreach ($param as $item) {
            $count_inner = 1;
            foreach($item as $itm){
                $letter_h = $this->getAlphabetCharacter($count_inner);
                $objPHPExcel->getActiveSheet()->setCellValue($letter_h.$it, strip_tags($itm));
                $count_inner++;
            }
            $it++;
        }

        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle('Page_1');

        $objPHPExcel->setActiveSheetIndex(0);

        return array(
            'excel_obj' => $objPHPExcel,
            'filename' => $filename
        );
    }

    function getAlphabetCharacter($index)
    {
        $alphabet = array(1=>'A',2=>'B',3=>'C',4=>'D',5=>'E',6=>'F',7=>'G',8=>'H',9=>'I',10=>'J',11=>'K',12=>'L',13=>'M',14=>'N',15=>'O',16=>'P'
        ,17=>'Q',18=>'R',19=>'S',20=>'T',21=>'U',22=>'V',23=>'W',24=>'X',25=>'Y',26=>'Z');
        return $alphabet[$index];
    }


    function getGroupModules(){
        $authUser = $this->Auth->user();
        if($authUser){
            $Module = ClassRegistry::init('Group');
            $userData = $Module->getGroupById($authUser['group_id']);
            $mod_arr = array();
            foreach($userData['Module'] as $mod){
                $mod_arr[$mod['id']] = array(
                    'name'=>$mod['name'],
                    'url'=>$mod['url'],
                    'icon'=>$mod['icon']
                );
            }
            return $mod_arr;
        }
        else{
            return false;
        }
    }


    function validateModules($mods){
        $authUser = $this->Auth->user();
        if($authUser){
            $request_controller = $this->request->params['controller'];
            $mods_arr = array('Dashboard','Users');
            foreach($mods as $mod){
                $url = $mod['url'];
                $url_arr = explode('/',$url);
                $controller = $url_arr[0];
                $mods_arr[] = $controller;
            }
            $return = in_array($request_controller,$mods_arr);
            return $return;
        }
        else{
            return true;
        }
    }


    function getModules(){
        $Module = ClassRegistry::init('Module');
        $userData = $Module->get();
        $mod_arr = array();
        foreach($userData as $mod){
            $mod_arr[] = array(
                'id'=>$mod['Module']['id'],
                'name'=>$mod['Module']['name'],
                'url'=>$mod['Module']['url'],
                'icon'=>$mod['Module']['icon']
            );
        }
        return $mod_arr;
    }

    function getBranches(){
        $Module = ClassRegistry::init('Branch');
        $userData = $Module->get();
        $mod_arr = array();
        foreach($userData as $mod){
            $mod_arr[] = array(
                'id'=>$mod['Branch']['id'],
                'name'=>$mod['Branch']['name']
            );
        }
        return $mod_arr;
    }

    function getDepartments(){
        $Module = ClassRegistry::init('Department');
        $userData = $Module->get();
        $mod_arr = array();
        foreach($userData as $mod){
            $mod_arr[] = array(
                'id'=>$mod['Department']['id'],
                'name'=>$mod['Department']['name']
            );
        }
        return $mod_arr;
    }

    function getGroups(){
        $Module = ClassRegistry::init('Group');
        $userData = $Module->get();
        $mod_arr = array();
        foreach($userData as $mod){
            $mod_arr[] = array(
                'id'=>$mod['Group']['id'],
                'name'=>$mod['Group']['name']
            );
        }
        return $mod_arr;
    }

    function getProjects(){
        $Module = ClassRegistry::init('Project');
        $userData = $Module->get();
        $mod_arr = array();
        foreach($userData as $mod){
            $mod_arr[] = array(
                'id'=>$mod['Project']['id'],
                'name'=>$mod['Project']['name']
            );
        }
        return $mod_arr;
    }

    function getStatus(){
        $Module = ClassRegistry::init('Status');
        $userData = $Module->get();
        $mod_arr = array();
        foreach($userData as $mod){
            $mod_arr[] = array(
                'id'=>$mod['Status']['id'],
                'name'=>$mod['Status']['name']
            );
        }
        return $mod_arr;
    }

    function getPriorities(){
        $Module = ClassRegistry::init('Priority');
        $userData = $Module->get();
        $mod_arr = array();
        foreach($userData as $mod){
            $mod_arr[] = array(
                'id'=>$mod['Priority']['id'],
                'name'=>$mod['Priority']['name']
            );
        }
        return $mod_arr;
    }

    function getGetAssignToList(){
        $Module = ClassRegistry::init('User');
        $userData = $Module->getWithMoreData();
        $mod_arr = array();
        foreach($userData as $mod){
            $mod_arr[] = array(
                'id'=>$mod['User']['id'],
                'name'=>$mod['User']['fname'].' '.$mod['User']['mname'].' '.$mod['User']['lname'].'  ('.$mod['Branch']['name'].')'
            );
        }
        return $mod_arr;
    }

    function getGetUserList(){
        $Module = ClassRegistry::init('User');
        $userData = $Module->getWithMoreData();
        $mod_arr = array();
        foreach($userData as $mod){
            $mod_arr[] = array(
                'id'=>$mod['User']['id'],
                'name'=>$mod['User']['fname'].' '.$mod['User']['mname'].' '.$mod['User']['lname'].'  ('.$mod['Branch']['name'].')'
            );
        }
        return $mod_arr;
    }


    function randomString($length)
    {
        $original_string = array_merge(range(0, 9), range('a', 'z'), range('A', 'Z'),array(time()),array('_','!','?','=','-','$','#'));
        $original_string = implode("", $original_string);
        return substr(str_shuffle($original_string), 0, $length);
    }

    function count_days_between_dates($smaller_date, $bigger_date)
    {
        $d1 = strtotime($smaller_date); //Smaller
        $d2 = strtotime($bigger_date); //Bigger
        $min_date = min($d1, $d2);
        $max_date = max($d1, $d2);
        $i = 0;
        while (($min_date = strtotime("+1 DAYS", $min_date)) <= $max_date) {
            $i++;
        }
        return $i;
    }


    function uploadFile($params)
    {  # Get the user session data
        $loggedUser = $this->Auth->user();

        # folder path structure
        $folder = $params['save_path'];//'files/tickets_attachments/';
        $folder_db = $params['folder'];//'tickets_attachments/';
        $file_size =  $params['file_size'];
        $prefix =  $params['file_name_prefix'];

        # setup directory pathname
        $folderAbsPath = WWW_ROOT . $folder;
        $folderRelPath = $folder;

        # create folders if it does not exist
        if (!is_dir($folderAbsPath)) {
            mkdir($folderAbsPath, 0777, true);
        }

        # get the file details
        $rawFile = $folderAbsPath . basename($params['file_name']);//($_FILES['uploadfile']['name']);
        $rawFile_rel = $folder_db . basename($params['file_name']); //($_FILES['uploadfile']['name']);

        // replace spaces with underscores
        $raw_file_name = str_replace(' ', '_', $params['file_name']);
        $fileName = str_replace(' ', '_', $rawFile);
        $fileName_rel = str_replace(' ', '_', $rawFile_rel);
        $fileType = $params['file_type'];//$_FILES['uploadfile']['type'];

        # list of permitted file types, this is only images but documents can be added
       // $permitted = array('image/gif', 'image/jpeg', 'image/pjpeg', 'image/png');

        $typeOk = true; # with the correct type the file will be uploaded or rejected.

       /* foreach ($permitted as $type) {
            if ($type == $fileType) {
                $typeOk = true;
                break;
            }
        }*/
        # if typeOk upload the file
        if ($typeOk) {
            # check whether the file already exists or not
            if (!file_exists($fileName)) {
                # upload the file
                if (move_uploaded_file($params['file_tmp_name'], $fileName)) {//$_FILES['uploadfile']['tmp_name']
                    return array('status'=>true, 'file_name'=>$raw_file_name, 'file_path'=>$fileName_rel,'msg'=>'File Uploaded.');
                } else {
                    return array('status'=>false,'msg'=>'File Cannot Be Uploaded.');
                }
            } else {
                return array('status'=>false,'msg'=>'File Already Exist.');
            }
        } else {
            return array('status'=>false,'msg'=>'Invalid File Format.');
        }
    }


    /*****  Reporting Section *******************************************/

    function getTicketConsolidated($start_dt,$end_dt,$user_id=null,$priority=null,$project=null,$department=null,$branch=null)
    {
        $Status = ClassRegistry::init('Status');
        $condition_array = array('Status.deleted' => 'n');
        $statuses = $Status->find('list', array(
            'fields'=>array('Status.id','Status.name'),
            'conditions' => $condition_array,
            'order' => array('Status.name' => 'asc'),
            'recursive' => -1
        ));

        $Ticket = ClassRegistry::init('Ticket');

        $condition_array = array('Ticket.created >=' => $start_dt, 'Ticket.created <=' => $end_dt, 'Ticket.deleted' => 'n');
        if ($user_id !=null) {
            $condition_array['Ticket.user_id']=$user_id;
        }
        if ($priority !=null) {
            $condition_array['Ticket.priority_id']=$priority;
        }
        if ($project !=null) {
            $condition_array['Ticket.project_id']=$project;
        }
        if ($department !=null) {
            $condition_array['Ticket.department_id']=$department;
        }
        if ($branch !=null) {
            $condition_array['Ticket.branch_id']=$branch;
        }

        $grid_data = $Ticket->find('all', array(
            'conditions' => $condition_array,
            'contain' => array(
                'Status' => array('fields' => array('Status.id', 'Status.name')),
            ),
            'order' => array('Ticket.id' => 'asc'),
            'recursive' => 1
        ));
        $grouped_data = array();
        if ($grid_data) {//If orders then group by monthly or yearly
            foreach ($grid_data as $data) {
                $order_date = $data['Ticket']['created'];
                $dates_arr = explode('-',$order_date);
                $year = $dates_arr[0];
                $month = $dates_arr[1];
                $group_index = $month.'/'.$year;
                $grouped_data[$group_index][] = $data;
            }
        }

        //debug($grouped_data);

        $tb_head = array('Time');
        $y_axis = array();
        foreach($statuses as $sta_id => $sta_name){
            $tb_head[] = $sta_name;
            $y_axis[$sta_id] =array('name'=>$sta_name,'data'=>array());
        }
        //Now group based on ticket status foreach group
        $gr_data = array();
        foreach ($grouped_data as $date => $arr) {
            foreach($arr as $arr2){
                $gr_data[$date][$arr2['Ticket']['status_id']]['name'] = $arr2['Status']['name'];
                $gr_data[$date][$arr2['Ticket']['status_id']]['data'][$arr2['Ticket']['id']] = $arr2;
            }
        }
        $tb_head = array_unique($tb_head);

        //debug($gr_data);

        $x_axis = array();
        $tb_body = array();
        $raw_data = array();
        $tl_total = 0;

        foreach ($gr_data as $x_ax => $arr) {
            $x_axis[] = $tb_row[] = $x_ax;
            $total = 0;
            foreach($statuses as $sta_id => $sta_name){
                $st = isset($arr[$sta_id])? $arr[$sta_id] : false;
                if($st){
                    $status_count = count($arr[$sta_id]['data']);
                    $y_axis[$sta_id]['data'][]= $tb_row[] =$status_count;
                }
                else{
                    $y_axis[$sta_id]['data'][]= $tb_row[] =0;
                }
            }
            /*foreach ($arr as $status_id => $status_data) {
                $status_name = $status_data['name'];
                $status_count = count($status_data['data']);

                $y_axis[$status_id]['data'][]= $tb_row[] =$status_count;
            }*/
            $tb_body[]=$tb_row;
        }

        $y_axis_finale = array();
        foreach ($y_axis as $x => $y) {
            $y_axis_finale[] = $y;
        }

        //$tr = array('Total',$tl_completed, $tl_processing + $tl_pending, $tl_cancelled );
        //$tr[]= $tl_total;
        //$tb_body[]=$tr;

        return array(
            'data' => array(
                'x-axis'=>$x_axis,
                'y-axis'=>$y_axis_finale
            ),
            'table'=>array(
                'thead'=>$tb_head,
                'tbody'=>$tb_body
            )
        );
    }



    function getTicketReport($start_dt,$end_dt,$ticket_id=null,$user_id=null,$status=null,$priority=null,$project=null,$department=null,$branch=null)
    {
       /* $Status = ClassRegistry::init('Status');
        $condition_array = array('Status.deleted' => 'n');
        $statuses = $Status->find('list', array(
            'fields'=>array('Status.id','Status.name'),
            'conditions' => $condition_array,
            'order' => array('Status.name' => 'asc'),
            'recursive' => -1
        ));*/

        $Ticket = ClassRegistry::init('Ticket');

        $condition_array = array('Ticket.created >=' => $start_dt, 'Ticket.created <=' => $end_dt, 'Ticket.deleted' => 'n');
        if ($ticket_id !=null) {
            $condition_array['Ticket.id']=$ticket_id;
        }
        if ($status !=null) {
            $condition_array['Ticket.status_id']=$status;
        }
        if ($user_id !=null) {
            $condition_array['Ticket.user_id']=$user_id;
        }
        if ($priority !=null) {
            $condition_array['Ticket.priority_id']=$priority;
        }
        if ($project !=null) {
            $condition_array['Ticket.project_id']=$project;
        }
        if ($department !=null) {
            $condition_array['Ticket.department_id']=$department;
        }
        if ($branch !=null) {
            $condition_array['Ticket.branch_id']=$branch;
        }

        $grid_data = $Ticket->find('all', array(
            'conditions' => $condition_array,
            'contain' => array(
                'Status' => array('fields' => array('Status.id', 'Status.name')),
            ),
            'order' => array('Ticket.id' => 'asc'),
            'recursive' => 1
        ));
        if($grid_data){
            return $grid_data;
        }
        else{
            return false;
        }

    }

}
